--EXEC SP_SEARCH_FOR_FINISHED_GOODS 0, -1, -1, ''
ALTER   PROC SP_SEARCH_FOR_FINISHED_GOODS

@SEARCH_BASEPRODUCT_ID INT,
@SEARCH_PROCESSSTEP_ID INT, 
@SEARCH_CUSTOMER_ID INT, 
@SEARCH_PROGRAM_CODE VARCHAR(50) 

AS

BEGIN

	DECLARE @RESULTS_TABLE TABLE ( Customer_Name VARCHAR(65), Base_Product_Name VARCHAR(50), ProceessStep_ID BIGINT, Part_Number VARCHAR(50), Program_Code VARCHAR(50), Customer_ID INT )
 	DECLARE @SQL_STRING NVARCHAR(3000)
	DECLARE @PARAM_DEFINITION NVARCHAR(500)

	DECLARE @CUSTOMER_ID INT
	DECLARE @C_NAME VARCHAR(65)
	DECLARE @FG_PROCESS_STEP_ID BIGINT
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @PROGRAMCODE VARCHAR(50)
	DECLARE @PARENT_PRODUCT_ID INT
	DECLARE @BASE_PRODUCT VARCHAR(50)

	--SET @SQL_STRING = N'DECLARE FINISHEDGOOD_CURSOR CURSOR for select Assembly_release_Id, Assembly_Release_No, Convert( VARCHAR, Assembly_release_date, 101), Part_Number, V_name, Status from Assm_release_table, Product, Vendor where Assm_release_table.ProcessStep_ID = Product.ProcessStep_ID AND Assm_release_table.TO_Vendor_ID = Vendor.Vendor_ID '

	--SET @SQL_STRING = N'DECLARE FINISHEDGOOD_CURSOR CURSOR FOR SELECT CUSTOMER_ID, C.C_NAME, P.PROCESSSTEP_ID, P.PART_NUMBER, P.P_PROGRAMCODE, P.P_PARENT_PRODUCT_ID, ( SELECT TOP 1 PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = P.P_PARENT_PRODUCT_ID )  AS BASE_PRODUCT FROM PRODUCT P, PROCESS_STEP PS, CUSTOMER C 
	--WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID IN ( SELECT FG_PROCESS_STEP_ID FROM FG_TO_FGI_MAP_TABLE ) AND P.P_CUSTOMERID = C.CUSTOMER_ID AND PS_TYPE_NAME = ''Finished Goods''' 

	SET @SQL_STRING = N'DECLARE FINISHEDGOOD_CURSOR CURSOR FOR SELECT FCMT.CUSTOMER_ID, C.C_NAME, P.PROCESSSTEP_ID, P.PART_NUMBER, P.P_PROGRAMCODE, P.P_PARENT_PRODUCT_ID, ( SELECT TOP 1 PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = P.P_PARENT_PRODUCT_ID )  AS BASE_PRODUCT FROM PRODUCT P, PROCESS_STEP PS, CUSTOMER C, FG_CUSTOMER_MAP_TABLE FCMT WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID IN ( SELECT FG_PROCESS_STEP_ID FROM FG_TO_FGI_MAP_TABLE ) AND PS.ProcessStep_ID = FCMT.ProcessStep_ID AND FCMT.CUSTOMER_ID = C.CUSTOMER_ID AND PS_TYPE_NAME = ''Finished Goods''' 

	SET @PARAM_DEFINITION = N'@PRM_BASEPRODUCT_ID INT, @PRM_PROCESSSTEP_ID INT, @PRM_CUSTOMER_ID INT, @PRM_PROGRAM_CODE VARCHAR(50)'

	IF @SEARCH_BASEPRODUCT_ID  <> 0
	BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND P.P_Parent_Product_Id = @Prm_BaseProduct_Id '
	END

	IF @SEARCH_PROCESSSTEP_ID  <> -1
	BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND P.PROCESSSTEP_ID = @Prm_ProcessStep_Id '
	END

	IF @SEARCH_CUSTOMER_ID  <> -1
	BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND FCMT.Customer_Id = @Prm_Customer_Id '
	END

	IF @SEARCH_PROGRAM_CODE  <> ''
	BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND P.P_ProgramCode LIKE @Prm_Program_Code'
	END
	
	EXECUTE SP_EXECUTESQL @SQL_STRING, @PARAM_DEFINITION, @PRM_BASEPRODUCT_ID = @SEARCH_BASEPRODUCT_ID, @PRM_PROCESSSTEP_ID = @SEARCH_PROCESSSTEP_ID, @PRM_CUSTOMER_ID = @SEARCH_CUSTOMER_ID, @PRM_PROGRAM_CODE = @SEARCH_PROGRAM_CODE

 	OPEN FINISHEDGOOD_CURSOR 
  	FETCH NEXT FROM FINISHEDGOOD_CURSOR INTO  @CUSTOMER_ID, @C_NAME, @FG_PROCESS_STEP_ID, @PART_NUMBER, @PROGRAMCODE, @PARENT_PRODUCT_ID, @BASE_PRODUCT
  	WHILE @@fetch_status = 0
  	BEGIN
		--Customer_Name VARCHAR(50), Base_Product_Name VARCHAR(50), Part_Number VARCHAR(50), Program_Code VARCHAR(50) 
		INSERT INTO @RESULTS_TABLE( Customer_Name, Base_Product_Name, ProceessStep_ID, Part_Number, Program_Code, Customer_ID )
		VALUES ( @C_NAME, @BASE_PRODUCT, @FG_PROCESS_STEP_ID, @PART_NUMBER, @PROGRAMCODE, @CUSTOMER_ID )

		SET @CUSTOMER_ID = NULL
		SET @C_NAME = NULL
		SET @FG_PROCESS_STEP_ID = NULL
		SET @PART_NUMBER = NULL
		SET @PROGRAMCODE = NULL
		SET @PARENT_PRODUCT_ID = NULL
		SET @BASE_PRODUCT = NULL

		FETCH NEXT FROM FINISHEDGOOD_CURSOR INTO  @CUSTOMER_ID, @C_NAME, @FG_PROCESS_STEP_ID, @PART_NUMBER, @PROGRAMCODE, @PARENT_PRODUCT_ID, @BASE_PRODUCT
	END
	CLOSE FINISHEDGOOD_CURSOR 
	DEALLOCATE FINISHEDGOOD_CURSOR
  
	SELECT * FROM  @RESULTS_TABLE order by Customer_Name
END